
SET QUOTED_IDENTIFIER OFF;
GO
USE [FootballManagement];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'TeamSet'
CREATE TABLE [dbo].[TeamSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FullName] nvarchar(max)  NOT NULL,
    [ShortName] nvarchar(max)  NOT NULL,
    [TeamCountry_Team_Id] int  NOT NULL,
    [PlayerTeam_Id] int  NOT NULL,
    [StaffTeam_Id] int  NOT NULL
);
GO

-- Creating table 'CountrySet'
CREATE TABLE [dbo].[CountrySet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'LeagueSet'
CREATE TABLE [dbo].[LeagueSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'SeasonSet'
CREATE TABLE [dbo].[SeasonSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [StartDate] nvarchar(max)  NOT NULL,
    [EndDate] nvarchar(max)  NOT NULL,
    [PlayerClub_Id] int  NOT NULL
);
GO

-- Creating table 'PlayerSet'
CREATE TABLE [dbo].[PlayerSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PlayerTeamSet'
CREATE TABLE [dbo].[PlayerTeamSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Player_Id] int  NOT NULL
);
GO

-- Creating table 'CitySet'
CREATE TABLE [dbo].[CitySet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Country_Id] int  NOT NULL
);
GO

-- Creating table 'VenueSet'
CREATE TABLE [dbo].[VenueSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [City_Id] int  NOT NULL
);
GO

-- Creating table 'StaffSet'
CREATE TABLE [dbo].[StaffSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'StaffTeamSet'
CREATE TABLE [dbo].[StaffTeamSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [HireDate] datetime  NOT NULL,
    [SackDate] datetime  NOT NULL,
    [Position] int  NOT NULL,
    [Staff_Id] int  NOT NULL
);
GO

-- Creating table 'SeasonTeam'
CREATE TABLE [dbo].[SeasonTeam] (
    [Season_Id] int  NOT NULL,
    [Team_Id] int  NOT NULL
);
GO

-- Creating table 'SeasonLeague'
CREATE TABLE [dbo].[SeasonLeague] (
    [Season_Id] int  NOT NULL,
    [League_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'TeamSet'
ALTER TABLE [dbo].[TeamSet]
ADD CONSTRAINT [PK_TeamSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CountrySet'
ALTER TABLE [dbo].[CountrySet]
ADD CONSTRAINT [PK_CountrySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'LeagueSet'
ALTER TABLE [dbo].[LeagueSet]
ADD CONSTRAINT [PK_LeagueSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SeasonSet'
ALTER TABLE [dbo].[SeasonSet]
ADD CONSTRAINT [PK_SeasonSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PlayerSet'
ALTER TABLE [dbo].[PlayerSet]
ADD CONSTRAINT [PK_PlayerSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PlayerTeamSet'
ALTER TABLE [dbo].[PlayerTeamSet]
ADD CONSTRAINT [PK_PlayerTeamSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'CitySet'
ALTER TABLE [dbo].[CitySet]
ADD CONSTRAINT [PK_CitySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'VenueSet'
ALTER TABLE [dbo].[VenueSet]
ADD CONSTRAINT [PK_VenueSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StaffSet'
ALTER TABLE [dbo].[StaffSet]
ADD CONSTRAINT [PK_StaffSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StaffTeamSet'
ALTER TABLE [dbo].[StaffTeamSet]
ADD CONSTRAINT [PK_StaffTeamSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Season_Id], [Team_Id] in table 'SeasonTeam'
ALTER TABLE [dbo].[SeasonTeam]
ADD CONSTRAINT [PK_SeasonTeam]
    PRIMARY KEY NONCLUSTERED ([Season_Id], [Team_Id] ASC);
GO

-- Creating primary key on [Season_Id], [League_Id] in table 'SeasonLeague'
ALTER TABLE [dbo].[SeasonLeague]
ADD CONSTRAINT [PK_SeasonLeague]
    PRIMARY KEY NONCLUSTERED ([Season_Id], [League_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [TeamCountry_Team_Id] in table 'TeamSet'
ALTER TABLE [dbo].[TeamSet]
ADD CONSTRAINT [FK_TeamCountry]
    FOREIGN KEY ([TeamCountry_Team_Id])
    REFERENCES [dbo].[CountrySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamCountry'
CREATE INDEX [IX_FK_TeamCountry]
ON [dbo].[TeamSet]
    ([TeamCountry_Team_Id]);
GO

-- Creating foreign key on [Season_Id] in table 'SeasonTeam'
ALTER TABLE [dbo].[SeasonTeam]
ADD CONSTRAINT [FK_SeasonTeam_Season]
    FOREIGN KEY ([Season_Id])
    REFERENCES [dbo].[SeasonSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Team_Id] in table 'SeasonTeam'
ALTER TABLE [dbo].[SeasonTeam]
ADD CONSTRAINT [FK_SeasonTeam_Team]
    FOREIGN KEY ([Team_Id])
    REFERENCES [dbo].[TeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SeasonTeam_Team'
CREATE INDEX [IX_FK_SeasonTeam_Team]
ON [dbo].[SeasonTeam]
    ([Team_Id]);
GO

-- Creating foreign key on [Season_Id] in table 'SeasonLeague'
ALTER TABLE [dbo].[SeasonLeague]
ADD CONSTRAINT [FK_SeasonLeague_Season]
    FOREIGN KEY ([Season_Id])
    REFERENCES [dbo].[SeasonSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [League_Id] in table 'SeasonLeague'
ALTER TABLE [dbo].[SeasonLeague]
ADD CONSTRAINT [FK_SeasonLeague_League]
    FOREIGN KEY ([League_Id])
    REFERENCES [dbo].[LeagueSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SeasonLeague_League'
CREATE INDEX [IX_FK_SeasonLeague_League]
ON [dbo].[SeasonLeague]
    ([League_Id]);
GO

-- Creating foreign key on [PlayerClub_Id] in table 'SeasonSet'
ALTER TABLE [dbo].[SeasonSet]
ADD CONSTRAINT [FK_PlayerClubSeason]
    FOREIGN KEY ([PlayerClub_Id])
    REFERENCES [dbo].[PlayerTeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlayerClubSeason'
CREATE INDEX [IX_FK_PlayerClubSeason]
ON [dbo].[SeasonSet]
    ([PlayerClub_Id]);
GO

-- Creating foreign key on [Player_Id] in table 'PlayerTeamSet'
ALTER TABLE [dbo].[PlayerTeamSet]
ADD CONSTRAINT [FK_PlayerClubPlayer]
    FOREIGN KEY ([Player_Id])
    REFERENCES [dbo].[PlayerSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlayerClubPlayer'
CREATE INDEX [IX_FK_PlayerClubPlayer]
ON [dbo].[PlayerTeamSet]
    ([Player_Id]);
GO

-- Creating foreign key on [PlayerTeam_Id] in table 'TeamSet'
ALTER TABLE [dbo].[TeamSet]
ADD CONSTRAINT [FK_PlayerTeamTeam]
    FOREIGN KEY ([PlayerTeam_Id])
    REFERENCES [dbo].[PlayerTeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlayerTeamTeam'
CREATE INDEX [IX_FK_PlayerTeamTeam]
ON [dbo].[TeamSet]
    ([PlayerTeam_Id]);
GO

-- Creating foreign key on [City_Id] in table 'VenueSet'
ALTER TABLE [dbo].[VenueSet]
ADD CONSTRAINT [FK_VenueCity]
    FOREIGN KEY ([City_Id])
    REFERENCES [dbo].[CitySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_VenueCity'
CREATE INDEX [IX_FK_VenueCity]
ON [dbo].[VenueSet]
    ([City_Id]);
GO

-- Creating foreign key on [Country_Id] in table 'CitySet'
ALTER TABLE [dbo].[CitySet]
ADD CONSTRAINT [FK_CityCountry]
    FOREIGN KEY ([Country_Id])
    REFERENCES [dbo].[CountrySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CityCountry'
CREATE INDEX [IX_FK_CityCountry]
ON [dbo].[CitySet]
    ([Country_Id]);
GO

-- Creating foreign key on [StaffTeam_Id] in table 'TeamSet'
ALTER TABLE [dbo].[TeamSet]
ADD CONSTRAINT [FK_StaffTeamTeam]
    FOREIGN KEY ([StaffTeam_Id])
    REFERENCES [dbo].[StaffTeamSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StaffTeamTeam'
CREATE INDEX [IX_FK_StaffTeamTeam]
ON [dbo].[TeamSet]
    ([StaffTeam_Id]);
GO

-- Creating foreign key on [Staff_Id] in table 'StaffTeamSet'
ALTER TABLE [dbo].[StaffTeamSet]
ADD CONSTRAINT [FK_StaffTeamStaff]
    FOREIGN KEY ([Staff_Id])
    REFERENCES [dbo].[StaffSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StaffTeamStaff'
CREATE INDEX [IX_FK_StaffTeamStaff]
ON [dbo].[StaffTeamSet]
    ([Staff_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------